drop table jms_dwd.dwd_tab_missrate_sitecode_summary;
--循环袋建包明细
create table jms_dwd.dwd_tab_missrate_sitecode_summary
(
    site_code           varchar(50) comment '转运中心编码',
    proxy_area_code     varchar(50) comment '代理区编码',
    site_name           varchar(100) comment '转运中心名称',
    proxy_area_name     varchar(100) comment '代理区编码名称',
    operate_qty         bigint comment '操作票数',
    errorseparate_qty   bigint comment '操作错分票数',
    sdmmiss_qty         bigint comment '三段码不全错分',
    sdmerror_qty        bigint comment '三段码错误错分',
    left_qty            bigint comment '操作错分',
    operate_cuofen_rate double comment '转运操作错分率',
    cuofen_rate         double comment '转运错分率',
    day_time            date comment '日期'
) engine = olap duplicate key(site_code, proxy_area_code)
comment "操作错分汇总"
partition by range(day_time)
(start ("2022-06-01") end ("2022-07-08") every (interval 1 day)
)
distributed by hash(`site_code`) buckets 4
properties (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "day",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "v2"
);